package syyrjx.jdbc;

import syyrjx.jdbc.util.JDBCTool;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.*;

public class QuestionDB {
    Connection conn = null;
    PreparedStatement ps = null;
    ResultSet rs = null;
    public boolean add(String question,String optionA,String optionB,String optionC,String optionD,String answer){
        boolean flag = false;
        try {
            conn = JDBCTool.getConnection();
            String sql = "insert questioninfo values(?,?,?,?,?,?,?)";
            ps = conn.prepareStatement(sql);
            ps.setInt(1,JDBCTool.queCount);
            ps.setString(2,question);
            ps.setString(3,optionA);
            ps.setString(4,optionB);
            ps.setString(5,optionC);
            ps.setString(6,optionD);
            ps.setString(7,answer);
            flag = ps.executeUpdate()>0;
            if(flag == true){
                JDBCTool.queCount++;
            }
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            JDBCTool.JDBCClose(rs,ps,conn);
        }
        return flag;
    }

    public Map<String,Object> show(int id){
        Map<String,Object> queInfo = new HashMap<>();
        try {
            conn = JDBCTool.getConnection();
            String sql = "select id,question,optionA,optionB,optionC,optionD,answer from questioninfo where id=?";
            ps = conn.prepareStatement(sql);
            ps.setInt(1,id);
            rs = ps.executeQuery();
            if(rs.next()){
                queInfo.put("id",rs.getInt("id"));
                queInfo.put("question",rs.getString("question"));
                queInfo.put("optionA",rs.getString("optionA"));
                queInfo.put("optionB",rs.getString("optionB"));
                queInfo.put("optionC",rs.getString("optionC"));
                queInfo.put("optionD",rs.getString("optionD"));
                queInfo.put("answer",rs.getString("answer"));
            }
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            JDBCTool.JDBCClose(rs,ps,conn);
        }
        return queInfo;
    }

    public Map<Integer,Map<String,Object>> show(){
        Map<Integer,Map<String,Object>> queInfo = new HashMap <>();
        for(int i = 0;i <JDBCTool.queCount;i++){
            queInfo.put(i,show(i));
        }
        return queInfo;
    }

    public boolean delete(Integer id){
        boolean flag = false;
        try {
            conn = JDBCTool.getConnection();
            String sql = "delete from questioninfo where id=?";
            ps = conn.prepareStatement(sql);
            ps.setInt(1,id);
            flag = ps.executeUpdate() > 0;
            if(flag){
                if(id < JDBCTool.queCount){
                    for(int i = id+1; i < JDBCTool.queCount;i++){
                        String sql1 = "update questioninfo set id=? where id=?";
                        ps = conn.prepareStatement(sql1);
                        ps.setInt(1,i-1);
                        ps.setInt(2,i);
                        ps.executeUpdate();
                    }
                }
                JDBCTool.queCount--;
            }
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            JDBCTool.JDBCClose(rs,ps,conn);
        }
        return flag;
    }

    public boolean modify(Integer id,String question,String optionA,String optionB,String optionC,String optionD,String answer){
        boolean flag = false;
        try {
            conn = JDBCTool.getConnection();
            String sql = "update questioninfo set question=?,optionA=?,optionB=?,optionC=?,optionD=?,answer=? where id=?";
            ps = conn.prepareStatement(sql);
            ps.setString(1,question);
            ps.setString(2,optionA);
            ps.setString(3,optionB);
            ps.setString(4,optionC);
            ps.setString(5,optionD);
            ps.setString(6,answer);
            ps.setInt(7,id);
            flag = ps.executeUpdate()>0;
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }finally {
            JDBCTool.JDBCClose(rs,ps,conn);
        }
        return flag;
    }

    public List<Map<String,Object>> getQuestion(int n){
        List<Map<String,Object>> questionsList = new ArrayList<>();
        try {
            conn = JDBCTool.getConnection();
            String sql = "select id,question,optionA,optionB,optionC,optionD,answer from questioninfo order by rand() desc limit 0,?";
            ps = conn.prepareStatement(sql);
            ps.setInt(1,n);
            rs = ps.executeQuery();
            while (rs.next()){
                Map<String,Object> questionMap = new HashMap<>();
                questionMap.put("id",rs.getInt("id"));
                questionMap.put("question",rs.getString("question"));
                questionMap.put("optionA",rs.getString("optionA"));
                questionMap.put("optionB",rs.getString("optionB"));
                questionMap.put("optionC",rs.getString("optionC"));
                questionMap.put("optionD",rs.getString("optionD"));
                questionMap.put("answer",rs.getString("answer"));
                questionsList.add(questionMap);
            }
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return questionsList;
    }

    public static void main(String[] args) {
        QuestionDB qdb = new QuestionDB();
        List<Map<String,Object>> questionList = qdb.getQuestion(3);
        for(Map<String,Object> a:questionList){
            System.out.println(a.get("id"));
            System.out.println(a.get("question"));
        }
    }

}
